package site.wtcc.originalfavor.func.utils;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class ExcelImportUtil {

    public static List<List<String>> excelImport(MultipartFile file) {
        List<List<String>> dataList = new ArrayList<List<String>>();
        try {
            InputStream in = file.getInputStream();
            Workbook hw = new XSSFWorkbook(in);
            // 获取第一个sheet页
            Sheet sheet = hw.getSheetAt(0);
            Row row = sheet.getRow(0);
            int colNum = row.getPhysicalNumberOfCells();

            for (int i = 0; i <= sheet.getLastRowNum(); i++) {
                // 循环获取行
                row = sheet.getRow(i);
                // 第一行为列头,跳过
                if (i == 0) {
                    continue;
                }
                List<String> list = new ArrayList<>();
                if (row != null) {
                    for (int j = 0; j < colNum; j++) {
                        Cell cell = row.getCell((short) j);
                        if (cell == null || "".equals(cell.toString().trim())) {
                            list.add(j, "");
                        } else {
                            list.add(j, getCellValue(cell));
                        }
                    }
                }
                if (list.size() != 0) {
                    dataList.add(list);
                }
            }
            if (dataList == null || dataList.get(0).isEmpty()) {
                throw new Exception("导入表格未找到数据，请检查后重新导入！");
            }
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return dataList;
    }

    public static String getCellValue(Cell cell) {
        CellType cellType = cell.getCellTypeEnum();
        String cellValue = "";
        if (cell == null || "".equals(cell.toString().trim())) {
            return null;
        }

        if (cellType == CellType.STRING) {
            cellValue = cell.getStringCellValue().trim();
            return cellValue = StringUtils.isEmpty(cellValue) ? "" : cellValue;
        }
        if (cellType == CellType.NUMERIC) {
            //判断日期类型
            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                // 获取日期类型的单元格的值
                Date d = cell.getDateCellValue();
                // 进行格式转换
                SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                cellValue = formatter.format(d);
            } else {  //否
                cell.setCellType(CellType.STRING);
                cellValue = cell.getStringCellValue();
            }
            return cellValue;
        }
        if (cellType == CellType.BOOLEAN) {
            cellValue = String.valueOf(cell.getBooleanCellValue());
            return cellValue;
        }
        return null;
    }

}
